package mwa.cs.dao;

import java.util.List;
import java.util.Map;

import mwa.cs.database.Database;
import mwa.cs.model.CustomerHistoryLog;

/**
 * A class used for managing customer_history_log
 */
public class CustomerHistoryLogDAO {

    /** a database of this customer_history_log */
    private Database db;

    /**
     * Instantiate a new object of this class
     * @param db a database
     */
    public CustomerHistoryLogDAO(Database db) {
        this.db = db;
    }
    
    /**
     * a method for adding a customer_history_log into database
     * @param poCustomerHistoryLog Customer_History_Log to add
     */
    public void addCustomerHistoryLog(CustomerHistoryLog poCustomerHistoryLog) {
        String vaSql = "INSERT INTO customer_history_log" + " (site_id, account, transaction_date, transaction_time, "
        		+ "transaction_type, old_description, new_description, modify_by) " 
        		+ "VALUES(?, ?, ?, ?, ?, ?, ?, ?)";
        try {
            db.insert(vaSql, poCustomerHistoryLog.getVnSiteId(),
            		poCustomerHistoryLog.getVaAccount(),
            		poCustomerHistoryLog.getVaTransactionDate(),
            		poCustomerHistoryLog.getVaTransactionTime(),
            		poCustomerHistoryLog.getVaTransactionType(),
            		poCustomerHistoryLog.getVaOldDescription(),
            		poCustomerHistoryLog.getVaNewDescription(),
            		poCustomerHistoryLog.getVnModifyBy());
        } catch (Exception e) {
        	throw new RuntimeException(e);
        }
    }
	
    /**
     * a method for counting customer_history_log by site_id, account
     * @param pnSiteId site that is wanted to count
     * @param paAccount account that is wanted to count
     * @return total of counted
     */
    public int getAllCountCustomerLogByAccount(int pnSiteId, String paAccount) {
        String vaSql = "SELECT count(*) FROM customer_history_log WHERE site_id = ? and account = ?";
        Map<String, Object> result = db.querySingle(vaSql, pnSiteId, paAccount);
        if (result != null) {
            return Integer.parseInt(result.get("COUNT(*)").toString());
        } else {
            return 0;
        }
    }
    
    public List<Map<String, Object>> findCustomerLogToTable(int pnSiteId, String paAccount, String paSortDirection, 
    		int pnStart, int pnEnd) {
    	String vaSql = "SELECT * FROM (SELECT transaction_date, transaction_time, transaction_type, "
    			+ "old_description, new_description, modify_by, ROWNUM rnum FROM "
    			+ "(SELECT * FROM customer_history_log WHERE site_id = ? and account = ? "
    			+ "ORDER BY transaction_date " + paSortDirection + ", transaction_time " + paSortDirection + ") "
    			+ "WHERE  ROWNUM <= ?) WHERE  rnum > ?";
    	List<Map<String, Object>> result = db.queryList(vaSql, pnSiteId, paAccount, pnEnd, pnStart);
        if (result != null) {
            return result;
        } else {
            return null;
        }
    }
    
    public List<Map<String, Object>> findAllCustomerLog(int pnSiteId, String paAccount, String paSortDirection) {
    	String vaSql = "SELECT * FROM customer_history_log WHERE site_id = ? and account = ? "
    			+ "ORDER BY transaction_date " + paSortDirection + ", transaction_time " + paSortDirection;
    	List<Map<String, Object>> result = db.queryList(vaSql, pnSiteId, paAccount);
        if (result != null) {
            return result;
        } else {
            return null;
        }
    }
    
    public boolean deleteCustomerLogByAccount(int pnSiteId, String paAccount) {
        String vaSql = "DELETE FROM customer_history_log WHERE site_id = ? and account = ?";
        try {
            db.update(vaSql, pnSiteId, paAccount);
            return true;
        } catch (Exception e) {
            return false;
        }
    }
    
}
